﻿using Sc.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sc.Persistence
{
    public class Car_UseCarApplicationDAO
    {
        #region  BasicMethod

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Car_UseCarApplication model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Car_UseCarApplication(");
            strSql.Append("StartAddress,EndAddress,StartTime,TotalCount,Remark,InstanceState,IsComplaint,ComplaintState,ApproalUsers,NodeCount,BussinessState,OrgzNo,UserId,ExDate,Reson,ZcTonnage,OldId,OldState)");
            strSql.Append(" values (");
            strSql.Append("@StartAddress,@EndAddress,@StartTime,@TotalCount,@Remark,@InstanceState,@IsComplaint,@ComplaintState,@ApproalUsers,@NodeCount,@BussinessState,@OrgzNo,@UserId,@ExDate,@Reson,@ZcTonnage,@OldId,@OldState)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@StartAddress", SqlDbType.NVarChar,150),
					new SqlParameter("@EndAddress", SqlDbType.NVarChar,150),
					new SqlParameter("@StartTime", SqlDbType.DateTime),
					new SqlParameter("@TotalCount", SqlDbType.Int,4),
					new SqlParameter("@Remark", SqlDbType.Text),
					new SqlParameter("@InstanceState", SqlDbType.Int,4),
					new SqlParameter("@IsComplaint", SqlDbType.Bit,1),
					new SqlParameter("@ComplaintState", SqlDbType.Int,4),
					new SqlParameter("@ApproalUsers", SqlDbType.NVarChar,100),
					new SqlParameter("@NodeCount", SqlDbType.Int,4),
					new SqlParameter("@BussinessState", SqlDbType.Bit,1),
                    new SqlParameter("@OrgzNo", SqlDbType.Int),
                    new SqlParameter("@UserId", SqlDbType.Int),
                    new SqlParameter("@ExDate", SqlDbType.DateTime),
                    new SqlParameter("@Reson", SqlDbType.NVarChar),
                    new SqlParameter("@ZcTonnage", SqlDbType.Decimal),
                    new SqlParameter("@OldId", SqlDbType.Int),
                    new SqlParameter("@OldState", SqlDbType.Int)};
            parameters[0].Value = model.StartAddress;
            parameters[1].Value = model.EndAddress;
            parameters[2].Value = model.StartTime;
            parameters[3].Value = model.TotalCount;
            parameters[4].Value = model.Remark;
            parameters[5].Value = model.InstanceState;
            parameters[6].Value = model.IsComplaint;
            parameters[7].Value = model.ComplaintState;
            parameters[8].Value = model.ApproalUsers;
            parameters[9].Value = model.NodeCount;
            parameters[10].Value = model.BussinessState;
            parameters[11].Value = model.OrgzNo;
            parameters[12].Value = model.UserId;
            parameters[13].Value = model.ExDate;
            parameters[14].Value = model.Reson;
            parameters[15].Value = model.ZcTonnage;
            parameters[16].Value = model.OldId;
            parameters[17].Value = model.OldState;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获取详情（单个实体）
        /// </summary>
        /// <param name="Id">Id</param>
        /// <returns></returns>
        public Car_UseCarApplication GetDetail(int id)
        {
            Car_UseCarApplication result = null;
            string sql = "SELECT * FROM Vw_CarUserApplication WHERE Id=@Id";
            SqlParameter parm = new SqlParameter("@Id", SqlDbType.Int, 4);
            parm.Value = id;

            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parm))
            {
                if (sdr.Read())
                {
                    result = new Car_UseCarApplication();
                    result = BuildObjectFromIDataReader(sdr);
                }
                sdr.Close();
            }
            return result;
        }
        /// <summary>
        /// 获取用车申请
        /// </summary>
        /// <param name="orgz">机构ID</param>
        /// <param name="bussinessState">是否被申诉</param>
        /// <param name="bussinessState">流程状态</param>
        /// <returns></returns>
        public IList<Car_UseCarApplication> GetCarUseList(int orgz, int instanceState, int bussinessState)
        {
            IList<Car_UseCarApplication> result = new List<Car_UseCarApplication>();
            string sql = "SELECT * FROM Vw_CarUserApplication WHERE InstanceState=@InstanceState AND BussinessState=@BussinessState AND OrgzNo=@OrgzNo order by StartTime DESC";
            SqlParameter[] parameters = {
					new SqlParameter("@InstanceState", SqlDbType.Int),
					new SqlParameter("@BussinessState", SqlDbType.Bit),
					new SqlParameter("@OrgzNo", SqlDbType.Int)};
            parameters[0].Value = instanceState;
            parameters[1].Value = bussinessState;
            parameters[2].Value = orgz;
            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql, parameters))
            {
                while (sdr.Read())
                {
                    result.Add(BuildObjectFromIDataReader(sdr));
                }
                sdr.Close();
            }
            return result;
        }
        /// <summary>
        /// 从IDataReader中读取数据，组装成实体
        /// </summary>
        /// <param name="dr">数据读取接口</param>
        /// <returns>Car_UseCarApplication实体</returns>
        /// <remarks>
        /// </remarks>
        private Car_UseCarApplication BuildObjectFromIDataReader(IDataReader dr)
        {
            try
            {
                Car_UseCarApplication obj = new Car_UseCarApplication();
                obj.Id = (dr["Id"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Id")));
                obj.StartAddress = (dr["StartAddress"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("StartAddress")));
                obj.EndAddress = (dr["EndAddress"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("EndAddress")));
                obj.StartTime = (dr["StartTime"] == System.DBNull.Value ? new DateTime(1900, 1, 1) : dr.GetDateTime(dr.GetOrdinal("StartTime")));
                obj.TotalCount = (dr["TotalCount"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("TotalCount")));
                obj.Remark = (dr["Remark"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Remark")));
                obj.InstanceState = (dr["InstanceState"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("InstanceState")));
                obj.IsComplaint = (dr["IsComplaint"] == System.DBNull.Value ? false : dr.GetBoolean(dr.GetOrdinal("IsComplaint")));
                obj.ComplaintState = (dr["ComplaintState"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("ComplaintState")));
                obj.ApproalUsers = (dr["ApproalUsers"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("ApproalUsers")));
                obj.NodeCount = (dr["NodeCount"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("NodeCount")));
                obj.BussinessState = (dr["BussinessState"] == System.DBNull.Value ? false : dr.GetBoolean(dr.GetOrdinal("BussinessState")));
                obj.UserId = (dr["UserId"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("UserId")));
                obj.OrgzNo = (dr["OrgzNo"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("OrgzNo")));
                obj.UserName = (dr["Name"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Name")));
                obj.ExDate = (dr["ExDate"] == System.DBNull.Value ? new DateTime(1900, 1, 1) : dr.GetDateTime(dr.GetOrdinal("ExDate")));
                obj.Reson = (dr["Reson"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("Reson")));
                obj.ZcTonnage = (dr["ZcTonnage"] == System.DBNull.Value ? 0M: dr.GetDecimal(dr.GetOrdinal("ZcTonnage")));
                obj.OldId = (dr["OldId"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("OldId")));
                obj.OldState = (dr["OldState"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("OldState")));
                return obj;
            }
            catch (Exception ex)
            {
                throw new Exception("从DataReader中读取数据失败，可能与相关的字段不存在有关！请检查！||" + ex.Message + "||" + ex.Source);
            }
        }
        /// <summary>
        /// 更新用车申请
        /// </summary>
        /// <param name="ent">业务数据</param>
        /// <returns></returns>
        public int Update(Car_UseCarApplication ent)
        {
            string sql = "UPDATE Car_UseCarApplication SET InstanceState=@InstanceState,ApproalUsers=@ApproalUsers,NodeCount=@NodeCount WHERE Id=@Id";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@InstanceState",SqlDbType.Int),
                new SqlParameter("@ApproalUsers",SqlDbType.NVarChar),
                new SqlParameter("@NodeCount",SqlDbType.Int),
                new SqlParameter("@Id",SqlDbType.Int)
            };
            parameters[0].Value = ent.InstanceState;
            parameters[1].Value = ent.ApproalUsers;
            parameters[2].Value = ent.NodeCount;
            parameters[3].Value = ent.Id;
            return DbHelperSQL.ExecuteSql(sql, parameters);
        }
        /// <summary>
        /// 用车被申诉更新被申诉数据状态
        /// </summary>
        /// <param name="id">原ID</param>
        /// <returns></returns>
        public int UpdateOldData(int id)
        {
            string sql = "UPDATE Car_UseCarApplication SET BussinessState=@BussinessState WHERE Id=@Id";
            SqlParameter[] parameters = new SqlParameter[]{
                new SqlParameter("@BussinessState",SqlDbType.Bit),
                new SqlParameter("@Id",SqlDbType.Int)
            };
            parameters[0].Value = 1;
            parameters[1].Value = id;
            return DbHelperSQL.ExecuteSql(sql, parameters);
        }
        #endregion  BasicMethod
        #region UserMethod
        /// <summary>
        /// 统计用户用车区域
        /// </summary>
        /// <param name="userId">用户</param>
        /// <returns></returns>
        public IList<Statistics> GetStatistics(int userId)
        {
            IList<Statistics> list = new List<Statistics>();
            string sql = " SELECT COUNT(*) AS Count,EndAddress FROM Car_UseCarApplication WHERE UserId=@UserId AND InstanceState>2 AND IsComplaint='false' GROUP BY EndAddress ";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@UserId",SqlDbType.Int)
            };
            param[0].Value = userId;
            using (SqlDataReader dr = DbHelperSQL.ExecuteReader(sql, param))
            {
                while (dr.Read())
                {
                    Statistics ent = new Statistics();
                    ent.Count = (dr["Count"] == System.DBNull.Value ? 0 : dr.GetInt32(dr.GetOrdinal("Count")));
                    ent.AreaName = (dr["EndAddress"] == System.DBNull.Value ? String.Empty : dr.GetString(dr.GetOrdinal("EndAddress")));
                    list.Add(ent);
                }
                dr.Close();
            }
            return list;
        }
        /// <summary>
        /// 取消用车
        /// </summary>
        /// <param name="id">用车申请ID</param>
        /// <returns></returns>
        public int CancelUseApplication(int id)
        {
            string sql = "Proc_Cancel_UseCarApplication";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@UseCarNo",SqlDbType.Int)
            };
            param[0].Value = id;
            return DbHelperSQL.RunProcedure(sql, param);
        }
        /// <summary>
        /// 拒绝派车
        /// </summary>
        /// <param name="id">用车申请ID</param>
        /// <param name="ent">意见表</param>
        /// <returns></returns>
        public int RefuseSendCar(int id,Opinions ent)
        {
            string sql = "Proc_Refuse_SendCar";
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@Id",SqlDbType.Int),
                new SqlParameter("@Source",SqlDbType.NVarChar),
                new SqlParameter("@UserId",SqlDbType.Int),
                new SqlParameter("@Context",SqlDbType.NVarChar)
            };
            param[0].Value = id;
            param[1].Value = ent.Source;
            param[2].Value = ent.UserId;
            param[3].Value = ent.Context;
            return DbHelperSQL.RunProcedure(sql, param);
        }
        #endregion
    }
}
